package com.b2c.repository;

import com.b2c.entity.result.PagingResponse;
import com.b2c.entity.KeyWordEntity;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;

import java.util.ArrayList;
import java.util.List;

@Repository
public class KeyWordRepository {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Transactional
    public PagingResponse<KeyWordEntity> getList(Integer pageIndex, Integer pageSize, String source, String year, String keyword,Long parentId,Integer categoryId) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT SQL_CALC_FOUND_ROWS kw.*,gc.`name` as categoryName FROM bd_keywords as kw");
        sb.append(" LEFT JOIN erp_goods_category gc on gc.id = kw.categoryId ");
        sb.append(" where kw.isDelete=0 ");

        List<Object> params = new ArrayList<>();
        if (StringUtils.isEmpty(source) == false) {
            sb.append(" AND kw.source = ?");
            params.add(source);
        }
        if (StringUtils.isEmpty(year) == false) {
            sb.append(" AND kw.include_year = ?");
            params.add(year);
        }
        if (StringUtils.isEmpty(keyword) == false) {
            sb.append(" AND kw.keyword LIKE ? ");
            params.add("%"+keyword+"%");
        }
        
        if(categoryId !=null ){
            sb.append(" AND kw.categoryId =? ");
            params.add(categoryId);
        }

        if(parentId !=null ){
            sb.append(" AND kw.parent_id =? ");
            params.add(parentId);
        }

        sb.append(" ORDER BY kw.sousuorenqi DESC LIMIT ?,? ");
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);

        List<KeyWordEntity> list = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(KeyWordEntity.class), params.toArray(new Object[params.size()]));
        Integer totalSize = getTotalSize();
        return new PagingResponse<>(pageIndex, pageSize, totalSize, list);
    }

    public List<KeyWordEntity> getParentList(Long parentId) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT SQL_CALC_FOUND_ROWS * FROM bd_keywords ");
        sb.append(" where parent_id=? ");

        List<Object> params = new ArrayList<>();
        params.add(parentId);

        return jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(KeyWordEntity.class), params.toArray(new Object[params.size()]));
    }


    protected int getTotalSize() {
        return jdbcTemplate.queryForObject("SELECT FOUND_ROWS() as row_num;", int.class);
    }
    public void addKeyWord(KeyWordEntity entity){
        String[] d = entity.getIncludeDate().split("-");
        String sql = "INSERT INTO bd_keywords (parent_id,category,keyword,sousuorenqi,dianjirenqi,dianjilv,zhifulv,include_year,include_month,include_date,sousuoredu,dianjiredu,depth) VALUE (?,?,?,?,?,?,?,?,?,?,?,?,?)";
        jdbcTemplate.update(sql,entity.getParentId(),entity.getCategory(),entity.getKeyword()
        ,entity.getSousuorenqi(),entity.getDianjirenqi(),entity.getDianjilv(),entity.getZhifulv(),
                d[0],d[1],entity.getIncludeDate(),entity.getSousuoredu(),entity.getDianjiredu(),entity.getDepth());
    }

    public KeyWordEntity getById(Long id){
        try{
            return jdbcTemplate.queryForObject("SELECT * FROM bd_keywords where id=?",new BeanPropertyRowMapper<>(KeyWordEntity.class),id);
        }catch (Exception e){
            return null;
        }
    }
}
